package com.xtedu.examproject.dao.impl;

import com.xtedu.examproject.dao.base.RoleDetailInfoDao;
import com.xtedu.examproject.dao.base.StudentDao;
import com.xtedu.examproject.entity.Student;
import com.xtedu.examproject.entity.StudentQuery;
import com.xtedu.examproject.util.DBUtil;
import com.xtedu.examproject.util.PageHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao, RoleDetailInfoDao {
    @Override
    public int insert(Student student) throws Exception {
        String sql = "insert into students(id_card,name,gender,create_date,class_id,age,edu_background,phone) values (?,?,?,?,?,?,?,?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, student.getIdCard());
            ps.setString(2, student.getName());
            ps.setBoolean(3, student.isGender());
            ps.setDate(4, student.getCreateDate());
            ps.setInt(5, student.getClassID());
            ps.setInt(6, student.getAge());
            ps.setString(7, student.getEduBackground());
            ps.setString(8, student.getPhone());
            return ps.executeUpdate();
        }
    }

    @Override
    public int update(Student student) throws Exception {
        String sql = "update students set id_card=?,name=?,gender=?,create_date=?,class_id=?,age=?,edu_background=?,phone=? where id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, student.getIdCard());
            ps.setString(2, student.getName());
            ps.setBoolean(3, student.isGender());
            ps.setDate(4, student.getCreateDate());
            ps.setInt(5, student.getClassID());
            ps.setInt(6, student.getAge());
            ps.setString(7, student.getEduBackground());
            ps.setString(8, student.getPhone());
            ps.setInt(9, student.getId());
            return ps.executeUpdate();
        }
    }

    @Override
    public int delete(Student student) throws Exception {
        String sql = "delete from students where id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, student.getId());
            return ps.executeUpdate();
        }
    }

    @Override
    public int deleteById(int id) throws Exception {
        return 0;
    }

    @Override
    public Student findById(int id) throws Exception {
        String sql = "select * from students where id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            Student student = new Student();
            while (rs.next()) {
                student.setId(rs.getInt("id"));
                student.setIdCard(rs.getString("id_card"));
                student.setName(rs.getString("name"));
                student.setCreateDate(rs.getDate("create_date"));
                student.setClassID(rs.getInt("class_id"));
                student.setAge(rs.getInt("age"));
                student.setEduBackground(rs.getString("edu_background"));
                student.setPhone(rs.getString("phone"));
                student.setGender(rs.getBoolean("gender"));
            }
            return student;
        }
    }

    @Override
    public List<Student> findAll() throws Exception {
        String sql = "select * from students";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            List<Student> students = new ArrayList<>();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setIdCard(rs.getString("id_card"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getBoolean("gender"));
                student.setCreateDate(rs.getDate("create_date"));
                student.setClassID(rs.getInt("class_id"));
                student.setAge(rs.getInt("age"));
                student.setEduBackground(rs.getString("edu_background"));
                student.setPhone(rs.getString("phone"));
                students.add(student);
            }
            return students;
        }
    }

    /**
     * 分页查询所有用户
     *
     * @param pageindex 页码
     * @param pagesize  每页显示数量
     * @return 页面对象
     */
    @Override
    public PageHelper findAllByPage(int pageindex, int pagesize) throws Exception {
        return null;
    }

    /**
     * 根据角色id查询所有用户
     *
     * @param pageindex 页码
     * @param pagesize  每页显示数量
     * @param roleIds   角色id
     * @return 页面对象
     */
    @Override
    public PageHelper findPageByRoleIds(int pageindex, int pagesize, int... roleIds) throws Exception {
        return null;
    }

    @Override
    public List showByOwnClazz(int userId) throws Exception {
        return List.of();
    }

    @Override
    public PageHelper showPageByOwnClazz(int userId, int pageIndex, int pageSize) throws Exception {
        return null;
    }

    /**
     * 获取老师所在班级的非用户学生
     *
     * @param teacherId 老师id
     * @return 学生集合
     */
    @Override
    public List<Student> findNonUserStu(int teacherId) throws Exception {
        String sql = "select s.* from students s left join classes c on s.class_id = c.id where c.teacher_id =? and not exists (select * from users u where u.role_id=4 and u.info_id = s.id)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, teacherId);
            ResultSet rs = ps.executeQuery();
            List<Student> students = new ArrayList<>();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setIdCard(rs.getString("id_card"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getBoolean("gender"));
                student.setCreateDate(rs.getDate("create_date"));
                student.setClassID(rs.getInt("class_id"));
                student.setAge(rs.getInt("age"));
                student.setEduBackground(rs.getString("edu_background"));
                student.setPhone(rs.getString("phone"));
                students.add(student);
            }
            return students;
        }
    }

    /**
     * 获取所有非用户学生
     *
     * @return 学生集合
     */
    @Override
    public List<Student> findNonUserStu() throws Exception {
        String sql = "select s.* from students s where not exists (select * from users u where u.role_id=4 and u.info_id = s.id)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            List<Student> students = new ArrayList<>();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setIdCard(rs.getString("id_card"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getBoolean("gender"));
                student.setCreateDate(rs.getDate("create_date"));
                student.setClassID(rs.getInt("class_id"));
                student.setAge(rs.getInt("age"));
                student.setEduBackground(rs.getString("edu_background"));
                student.setPhone(rs.getString("phone"));
                students.add(student);
            }
            return students;
        }
    }

    /**
     * 显示用户详细信息
     *
     * @param userId 用户id
     * @return 用户详细信息
     */
    @Override
    public Student showDetailInfo(int userId) throws Exception {
        String sql = "select s.* from (select * from users u where role_id = 4) u left join students s on u.info_id = s.id where u.id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            // 填入参数
            ps.setInt(1, userId);

            ResultSet rs = ps.executeQuery();
            Student student = new Student();
            while (rs.next()) {
                student.setId(rs.getInt("id"));
                student.setIdCard(rs.getString("id_card"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getBoolean("gender"));
                student.setCreateDate(rs.getDate("create_date"));
                student.setClassID(rs.getInt("class_id"));
                student.setAge(rs.getInt("age"));
                student.setEduBackground(rs.getString("edu_background"));
            }
            return student;
        }
    }


    /*
     * 接收前端的查询条件， stu最少1个参数，最多4个参数
     * String[] params = new String[]{"id_card", "name",  "phone"，"gender", "edu_background","class_id"};
     *存在问题 因为当性别字段为空时，criteria.isGender() 返回了 false（boolean 的默认值），而不是 null。
     * */
    public List<Student> searchStudents(Student criteria) throws SQLException {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM students WHERE " +
                "(? IS NULL OR name LIKE CONCAT('%', ?, '%')) AND " +
                "(? IS NULL OR id_card LIKE CONCAT('%', ?, '%')) AND " +
                "(? IS NULL OR phone LIKE CONCAT('%', ?, '%')) AND " +
                "(? IS NULL OR class_id = ?) AND " +
                "(? IS NULL OR edu_background = ?) AND " +
                "(? IS NULL OR gender = ?)";
        try (Connection conn = DBUtil.getConnection()) {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            // 设置参数（每个条件对应两个参数：一个检查NULL，一个实际值）
            pstmt.setString(1, criteria.getName());
            pstmt.setString(2, criteria.getName());
            pstmt.setString(3, criteria.getIdCard());
            pstmt.setString(4, criteria.getIdCard());
            pstmt.setString(5, criteria.getPhone());
            pstmt.setString(6, criteria.getPhone());
            pstmt.setObject(7, criteria.getClassID());
            pstmt.setObject(8, criteria.getClassID());
            pstmt.setString(9, criteria.getEduBackground());
            pstmt.setString(10, criteria.getEduBackground());
            //性别处理：如果是null，查询时会忽略该条件
            pstmt.setObject(11, criteria.isGender());
            pstmt.setObject(12, criteria.isGender());
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setIdCard(rs.getString("id_card"));
                student.setName(rs.getString("name"));
                // 处理数据库中的boolean值
                student.setGender(rs.getBoolean("gender"));
                student.setCreateDate(rs.getDate("create_date"));
                student.setClassID(rs.getInt("class_id"));
                student.setAge(rs.getInt("age"));
                student.setEduBackground(rs.getString("edu_background"));
                student.setPhone(rs.getString("phone"));
                students.add(student);
            }
        } catch (SQLException e) {
            System.out.println("查询学生信息失败：" + e.getMessage());
            e.printStackTrace();

        }

        return students;
    }


    public List<Student> searchStudents(String keyWord, int gender, String classID, String eduBackground) throws SQLException {
        List<Student> students = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM students");
        List<Object> params = new ArrayList<>();

        List<String> conditions = new ArrayList<>();

        // 构建条件列表
        if (keyWord != null && !keyWord.trim().isEmpty()) {
            conditions.add("(name LIKE CONCAT('%', ?, '%') OR id_card LIKE CONCAT('%', ?, '%') OR phone LIKE CONCAT('%', ?, '%'))");
            params.add(keyWord);
            params.add(keyWord);
            params.add(keyWord);
        }

        if (gender != -1) {
            conditions.add("gender = ?");
            params.add(gender);
        }
        if (gender == -1) {
            conditions.add("gender=gender");
        }

        if (classID != null && !classID.trim().isEmpty()) {
            conditions.add("class_id = ?");
            params.add(classID);
        }

        if (eduBackground != null && !eduBackground.trim().isEmpty()) {
            conditions.add("edu_background = ?");
            params.add(eduBackground);
        }

        // 拼接WHERE子句
        if (!conditions.isEmpty()) {
            sql.append(" WHERE ").append(String.join(" AND ", conditions));
        }

        try (Connection conn = DBUtil.getConnection()) {
            PreparedStatement pstmt = conn.prepareStatement(sql.toString());

            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setIdCard(rs.getString("id_card"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getBoolean("gender"));
                student.setCreateDate(rs.getDate("create_date"));
                student.setClassID(rs.getInt("class_id"));
                student.setAge(rs.getInt("age"));
                student.setEduBackground(rs.getString("edu_background"));
                student.setPhone(rs.getString("phone"));
                students.add(student);
            }
        } catch (SQLException e) {
            System.out.println("查询学生信息失败：" + e.getMessage());
            e.printStackTrace();
        }
        return students;
    }

    @Override
    public PageHelper<Student> findStudentsByClassIdByPage(int pageIndex, int pageSize, int classId) throws Exception {
        String sql = "SELECT * FROM students WHERE class_id = ? limit ?,?";
        List<Student> students = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, classId);
            ps.setInt(2, (pageIndex - 1) * pageSize);
            ps.setInt(3, pageSize);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setIdCard(rs.getString("id_card"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getBoolean("gender"));
                student.setCreateDate(rs.getDate("create_date"));
                student.setClassID(rs.getInt("class_id"));
                student.setAge(rs.getInt("age"));
                student.setEduBackground(rs.getString("edu_background"));
                student.setPhone(rs.getString("phone"));
                students.add(student);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        String sql1 = "SELECT ceil(count(*)/?) FROM students WHERE class_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql1)) {
            ps.setInt(1, pageSize);
            ps.setInt(2, classId);
            ResultSet rs1 = ps.executeQuery();
            int totalPages = 0;
            if (rs1.next()) {
                totalPages = rs1.getInt(1);
            }
            return new PageHelper<>(students, pageIndex, pageSize, totalPages, "", pageIndex);
        }
    }

}
